package com.aisb.manage;

import com.aisb.core.Result;
import com.aisb.core.log.LogVisit;
import com.aisb.core.util.DateUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.shiro.authz.annotation.RequiresRoles;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import java.util.*;

@Controller
@RequestMapping("/manage/db")
public class DBController {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @RequestMapping(value = "/index")
    @RequiresRoles("admin")
    @LogVisit("数据库管理首页")
    public String db(Model model) {
        String sql = "show tables;";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);

        Set<String> tables = new HashSet();
        for (Map m : list) {
            for (Object key : m.keySet()) {
                tables.add((String) m.get(key));
            }
        }

        List rList = new ArrayList();

        for (String table : tables) {
            sql = "select count(*) from " + table;
            Object count = jdbcTemplate.queryForObject(sql, String.class);
            Map r = new HashMap();
            r.put("name", table);
            r.put("count", count);
            rList.add(r);
        }

        Collections.sort(rList, new Comparator<Map>() {
            public int compare(Map o1, Map o2) {
                String name1 = (String) o1.get("name");
                String name2 = (String) o2.get("name");
                return name1.compareTo(name2);
            }
        });

        model.addAttribute("tables", rList);

        return "manage/db";
    }


    @RequestMapping(value = "/backup")
    @RequiresRoles("admin")
    @ResponseBody
    public Result backup(@RequestParam Map param) {
        String table = (String) param.get("table");
        if (StringUtils.isBlank(table)) {
            return new Result(false, "");
        }

        try {
            String tableNew = table + "_" + DateUtil.getInstance().getToday(null);
            String sql = "create table " + tableNew + " as select * from " + table;
            jdbcTemplate.execute(sql);
        } catch (DataAccessException e) {
            e.printStackTrace();
            return new Result(false, "");
        }

        return new Result(true, "");
    }

    @RequestMapping(value = "/view")
    @RequiresRoles("admin")
    @ResponseBody
    public Result view(@RequestParam Map param) {
        String table = (String) param.get("table");
        if (StringUtils.isBlank(table)) {
            return new Result(false, "");
        }

        String sql = "select * from " + table;
        SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql);
        SqlRowSetMetaData metaData = rowSet.getMetaData();
        int columnCount = metaData.getColumnCount();

        List rList = new ArrayList();
        for (int i = 1; i <= columnCount; i++) {
            Map<String, String> fieldMap = new HashMap<String, String>();
            fieldMap.put("columnName", metaData.getColumnName(i));
            fieldMap.put("columnType", String.valueOf(metaData.getColumnType(i)));
            fieldMap.put("columnTypeName", metaData.getColumnTypeName(i));
            fieldMap.put("catalogName", metaData.getCatalogName(i));
            fieldMap.put("columnClassName", metaData.getColumnClassName(i));
            fieldMap.put("columnLabel", metaData.getColumnLabel(i));
            fieldMap.put("precision", String.valueOf(metaData.getPrecision(i)));
            fieldMap.put("scale", String.valueOf(metaData.getScale(i)));
            fieldMap.put("schemaName", metaData.getSchemaName(i));
            fieldMap.put("tableName", metaData.getTableName(i));
            fieldMap.put("schemaName", metaData.getSchemaName(i));
            rList.add(fieldMap);
        }

        return new Result(true, rList);
    }

}
